﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using DevExpress.Utils.CommonDialogs;
using DevExpress.XtraEditors;

namespace DXApplication1月报表
{
    public partial class XtraForm半成品查询 : DevExpress.XtraEditors.XtraForm
    {
       Guid guid ;
        string tempName = "";
        public XtraForm半成品查询()
        {
            InitializeComponent();
        }
        string 项目号 = "";
        string 料号 = "";

        private void simpleButton查询_Click(object sender, EventArgs e)
        {
            初始化表格列名();
            if (this.uiTextBox项目号.Text == "")
            {
                XtraMessageBox.Show("请输入项目号");
                return;
            }

            项目号 = this.uiTextBox项目号.Text.Trim();
            料号 = textEdit料号.Text.Trim();    

            //string[] s = uiTextBox项目号.Text.Trim().Split(new[] { '-' }, 2);
            //项目号 = s[0];
            //if (s.Length > 1)
            //{
            //    料号 = s[1];
            //}
            //else
            //{
            //    料号 = "";
            //}

            guid = Guid.NewGuid();
            tempName = "temp_"+ guid.ToString("N");
            查询原始表到temp表();
            DataTable tb = 从temp表到Z_半成品金额报表();
            //从表格筛选料号  
            if (料号 != "")
            {
                string filter = $"料号 LIKE '%{料号}%'";
                DataRow[] rows = tb.Select(filter);

                // 将筛选结果复制到新 DataTable
                DataTable filteredTable = tb.Clone(); // 复制结构
                foreach (DataRow row in rows)
                {
                    filteredTable.ImportRow(row); // 导入符合条件的行
                }


                //gridView1.Columns.Clear();
                this.gridControl1.DataSource = filteredTable;
            }
            else
            {
                this.gridControl1.DataSource = tb;
            }
           
            this.gridControl1.Refresh();
            清空中间表();
           

        }


        private void 初始化表格列名()
        { 
            gridView1.Columns.Clear();
            gridView1.Columns.AddVisible("项目号", "项目号");
            gridView1.Columns.AddVisible("料号", "料号");
            gridView1.Columns.AddVisible("名称", "名称");
            gridView1.Columns.AddVisible("材料数量", "材料数量");
            gridView1.Columns.AddVisible("材料单价", "材料单价");
            gridView1.Columns.AddVisible( "材料金额", "材料合计");

            gridView1.Columns.AddVisible("加工数量", "加工数量");
            gridView1.Columns.AddVisible("加工单价", "加工单价");
            gridView1.Columns.AddVisible( "加工金额", "加工合计");
            gridView1.Columns.AddVisible("合计单价", "零件单价");
            gridView1.Columns.AddVisible("合计金额", "零件合计");

        }
        private void 清空中间表()
        {
            string sql = $"drop table {tempName}";
            SqlHelper.SqlHelper.ExecuteNonQuery(sql);
            sql = $"delete from Z_半成品金额报表 where guid = '{guid.ToString()}'";
            sql = $"delete from Z_半成品金额报表";
            SqlHelper.SqlHelper.ExecuteNonQuery(sql);
        }
      

        private DataTable 查询原始表到temp表()
        {
            string sql项目号 = "";
           

            if (项目号 != "")
            {
                sql项目号 = " and job_code = '" + 项目号 + "'";
            }
           
            string sqlU8 = 查询u8原始表到temp表(); 

            string sql查询 = $@"select * into {tempName}
                          from (
                          SELECT  newid() guid,'mes' 账套, getdate() dPODate,
                            seihin_rec_id,    job_code,    buhin_rec_id,
                           CASE 
                                WHEN part_code LIKE job_code + '%' THEN 
                                    STUFF(part_code, 1, LEN(job_code)+ 1, '')
                                ELSE part_code
                            END AS part_code, part_code orginal_part_code ,   part_name,'' cInvStd,    part_qty,    part_jdl_rate,    part_uprice,    part_amount,
	                        cast('' as nvarchar(40))分类,cast('' as nvarchar(40)) 主part_code,cast('' as nvarchar(40)) 是主part_code
                        FROM 
                            OPENROWSET('SQLOLEDB', '192.168.1.13'; 'bb'; 'qwerasdf@', epc.dbo.VIEW_MES_ERP_JOBPART_PROGRESS_COST) 
                        WHERE 1=1
                          {sql项目号}  
                            AND part_jdl_rate >= 1
                            union all
                            {sqlU8}

                        )t
                        ";

            SqlHelper.SqlHelper.ExecuteNonQuery(sql查询);

           


            提取物料号();




            string sql = $"select * from {tempName} order by job_code,主part_code";
            DataTable tb = new DataTable();
            tb = SqlHelper.SqlHelper.ExecuteDataset(sql).Tables[0];
            return tb;
        }

        private string 查询u8原始表到temp表()
        {
            string sql查询 = "";
           string sqlmesU8对照 = $"select [id],[MES项目号],[U8项目号],[已确定] from Z_MES_U8项目号对照 where MES项目号 = '{项目号}' and [已确定] = 1";
            DataTable dt = SqlHelper.SqlHelper.ExecuteDataset(sqlmesU8对照).Tables[0];
            if (dt != null && dt.Rows.Count > 0)
            {
                List<string> list = dt.AsEnumerable()
                      .Select(row => row.Field<string>("U8项目号"))
                      .ToList();
                list.Add(项目号);
                string instr = string.Join("','", list);

                this.Text += $"{项目号}-{instr}";
                //XtraMessageBox.Show($"根据MES_U8项目号对照设置的对照表，将从U8查询一下项目号\r\n{instr}");

                sql查询 = $@"
                              SELECT  newid() guid,账套,	dPODate,'' seihin_rec_id, '{项目号}'	csocode,''buhin_rec_id ,
	                          CASE 
                                WHEN cInvCode LIKE '{项目号}' + '%' THEN 
                                    STUFF(cInvCode, 1, LEN('{项目号}')+ 1, '')
                                ELSE cInvCode
                            END AS cInvCode	,cInvCode orginal_part_code,	cInvName,	cInvStd,	iQuantity,1 part_jdl_rate,	iUnitPrice,isum,
	                        cast('' as nvarchar(40))分类,cast('' as nvarchar(40)) 主part_code,cast('' as nvarchar(40)) 是主part_code
	                          FROM  View_采购订单 WHERE 1=1
                          and csocode in ('{instr}')
                          AND cInvCode LIKE '%*%'
     
                        ";
            }
            else
            {
                //XtraMessageBox.Show($"没有设置MES_U8项目号对照，将从U8查询一下项目号\r\n{项目号}-[A-Z]");
                this.Text += $"{项目号}-[A-Z]";
                sql查询 = $@"
                              SELECT  newid() guid,账套,	dPODate,'' seihin_rec_id,	csocode,''buhin_rec_id ,
	                          CASE 
                                WHEN cInvCode LIKE csocode + '%' THEN 
                                    STUFF(cInvCode, 1, LEN(csocode)+ 1, '')
                                ELSE cInvCode
                            END AS cInvCode	,cInvCode orginal_part_code,	cInvName,	cInvStd,	iQuantity,1 part_jdl_rate,	iUnitPrice,isum,
	                        cast('' as nvarchar(40))分类,cast('' as nvarchar(40)) 主part_code,cast('' as nvarchar(40)) 是主part_code
	                          FROM  View_采购订单 WHERE 1=1
                          and csocode = '{项目号}'   OR csocode LIKE '{项目号}-[A-Z]'
                          AND cInvCode LIKE '%*%'
                       
                        ";
            }
            return sql查询;
            //SqlHelper.SqlHelper.ExecuteNonQuery(sql查询);
        }

        private void 提取物料号()
        {
            string sql去空格 = $@"  update {tempName} set part_code = LTRIM(RTRIM(part_code)),主part_code = LTRIM(RTRIM(part_code));";
            SqlHelper.SqlHelper.ExecuteNonQuery(sql去空格);

            //最开始的提取物料号
            string sql1 = $@"update {tempName} set 主part_code = 
                           CASE 
                               -- 提取以 纯数字-数字-数字 开头（如 20006-001-1）
                               WHEN part_code LIKE '[0-9]%[-][0-9]%[-][0-9]%' and part_code  like 'miss%'
                                    THEN LEFT(part_code, PATINDEX('%[^0-9-]%', part_code + 'X') - 1)

                               -- 提取以 纯数字-数字 开头（如 20006-3），但不是三段的
                               WHEN part_code LIKE '[0-9]%[-][0-9]%' and part_code  like 'miss%'
                                    THEN LEFT(part_code, PATINDEX('%[^0-9-]%', part_code + 'X') - 1)

                               -- 提取纯数字（如 20006）或数字+字母（如 20006MISS）
                               ELSE LEFT(part_code, PATINDEX('%[^0-9]%', part_code + 'X') - 1)
                           END -- AS 取数结果
                    FROM {tempName} where part_code not like '%.%'";
            //SqlHelper.SqlHelper.ExecuteNonQuery(sql1);

            //取#、MISS、D前面的
            string sql = $@"UPDATE t
                        SET 主part_code =
                            CASE 
                                WHEN y.最大位置 > 0 
                                    THEN LEFT(t.part_code, y.最大位置 - 1)
                                ELSE t.part_code
                            END
                        FROM {tempName} t
                        CROSS APPLY (
                            SELECT 
                                -- 找最后一个 #
                                CASE WHEN CHARINDEX('#', REVERSE(t.part_code)) > 0 
                                     THEN LEN(t.part_code) - CHARINDEX('#', REVERSE(t.part_code)) +1 
                                     ELSE 0 END AS pos1,

                                -- 找最后一个 MISS （反着找）
                                CASE WHEN CHARINDEX('SSIM', REVERSE(t.part_code)) > 0 
                                     THEN LEN(t.part_code) - (CHARINDEX('SSIM', REVERSE(t.part_code)) +3) +1 
                                     ELSE 0 END AS pos2,

                                -- 找最后一个 D
                                CASE WHEN CHARINDEX('D', REVERSE(t.part_code)) > 0 
                                     THEN LEN(t.part_code) - CHARINDEX('D', REVERSE(t.part_code)) +1 
                                     ELSE 0 END AS pos3
                        ) p
                        CROSS APPLY (
                            SELECT 
                                CASE 
                                    WHEN p.pos1 >= p.pos2 AND p.pos1 >= p.pos3 THEN p.pos1
                                    WHEN p.pos2 >= p.pos1 AND p.pos2 >= p.pos3 THEN p.pos2
                                    WHEN p.pos3 >= p.pos1 AND p.pos3 >= p.pos2 THEN p.pos3
                                    ELSE 0 
                                END AS 最大位置
                        ) y
                        WHERE t.part_code NOT LIKE '%.%' and (t.part_code like '%#%' or t.part_code like '%miss%' or t.part_code like '%D%')  ";
            SqlHelper.SqlHelper.ExecuteNonQuery(sql);

            //带.的处理
            string sql3 = $@"
                              
                                update {tempName} set 主part_code = part_code where part_code like '%.%';

                                UPDATE {tempName}
                                SET 主part_code = 
                                    CASE 
                                        WHEN part_code LIKE '%.%-%' 
                                            THEN SUBSTRING(part_code, 1, LEN(part_code) - CHARINDEX('-', REVERSE(part_code)))
                                        ELSE part_code
                                    END 
                                FROM {tempName} where  part_code LIKE '%.%-%' ;

                                UPDATE {tempName}
                                    SET 主part_code = 
                                        CASE 
                                            WHEN part_code LIKE '%.%#[0-9]%' 
                                                THEN SUBSTRING(part_code, 1, LEN(part_code) - CHARINDEX('#', REVERSE(part_code)))
                                            ELSE part_code
                                        END 
                                    FROM {tempName} where  part_code LIKE '%.%#[0-9]%' ;

                                UPDATE {tempName}
                                SET 主part_code = 
                                CASE 
                                WHEN part_code LIKE '%.%-[0-9]*%' 
                                THEN SUBSTRING(part_code, 1, LEN(part_code) - CHARINDEX('*', REVERSE(part_code)))
                                ELSE part_code
                                END 
                                FROM {tempName} where  part_code  LIKE '%.%-[0-9]*%' 

                                UPDATE {tempName}
                                SET 主part_code = 
                                    CASE 
                                        WHEN part_code LIKE '%.%*%' 
                                            THEN SUBSTRING(part_code, 1, LEN(part_code) - CHARINDEX('*', REVERSE(part_code)))
                                        ELSE part_code
                                    END 
                                FROM {tempName}
                                where 主part_code like '%*' and    part_code LIKE '%.%*%' ";

            SqlHelper.SqlHelper.ExecuteNonQuery(sql3);

         

            string sql空白的就用原来的 = $@"  update {tempName} set 主part_code = part_code where isnull(主part_code,'')='';";
            SqlHelper.SqlHelper.ExecuteNonQuery(sql空白的就用原来的);

            string sql2 = $@"update  {tempName} set 主part_code =
                            CASE 
                                WHEN RIGHT(主part_code, 1) = '-' 
                                THEN LEFT(主part_code, LEN(主part_code) - 1)
                                ELSE 主part_code
                            END 
                        FROM {tempName};";
            SqlHelper.SqlHelper.ExecuteNonQuery(sql2);

            //去掉最后一个*
            string sql去星号 = $@"update  {tempName} set 主part_code =
                            CASE 
                                WHEN RIGHT(主part_code, 1) = '*' 
                                THEN LEFT(主part_code, LEN(主part_code) - 1)
                                ELSE 主part_code
                            END 
                        FROM {tempName};";
            SqlHelper.SqlHelper.ExecuteNonQuery(sql去星号);

        }

        private DataTable 从temp表到Z_半成品金额报表() 
        {
            string sql = $@"insert into Z_半成品金额报表 (guid,项目号,料号,原始合计金额,材料数量,材料单价,材料金额,加工数量,加工单价,加工金额,合计单价,合计金额)
                            select '{guid.ToString()}', job_code 项目号,主part_code	料号,		sum(part_amount)	合计金额	,0,0,0,0,0,0,0,0
                            from {tempName}  
                            group by job_code,主part_code
                            order by 主part_code";
            SqlHelper.SqlHelper.ExecuteNonQuery(sql);

            string sql2 = $@"
                update Z_半成品金额报表 set 名称 = {tempName}.part_name
                from Z_半成品金额报表 join {tempName}  on 主part_code = 料号
                where 账套 = 'MES'

                update Z_半成品金额报表 set 名称 = {tempName}.part_name
                from Z_半成品金额报表 join {tempName}  on 主part_code = 料号
                where 名称 IS NULL
              ";

            SqlHelper.SqlHelper.ExecuteNonQuery(sql2);

            更新数量();

             sql = $"select * from Z_半成品金额报表 where guid = '{guid.ToString()}' order by 项目号,料号";
            DataTable tb = new DataTable();
            tb = SqlHelper.SqlHelper.ExecuteDataset(sql).Tables[0];
            return tb;
        }

        private void 更新数量()
        {
            //数量都使用不含miss的mes数量
            string sql加工数量 = $@"  update Z_半成品金额报表 set 加工数量 = part_qty,材料数量 = part_qty
                                    from Z_半成品金额报表 join (select 主part_code, sum(part_qty)part_qty from {tempName} where 账套 = 'mes' and part_code not like '%miss%' and part_code not like '%#%' group by 主part_code)temp_123  on 主part_code = 料号;";
            SqlHelper.SqlHelper.ExecuteNonQuery(sql加工数量);

            //mes金额使用所有的
            string sqlmes金额 = $@"update Z_半成品金额报表 set 加工金额 =part_amount
                                from Z_半成品金额报表 join (select 主part_code, sum(part_amount)part_amount from {tempName} where 账套 = 'mes' and part_code not like '%#%' group by 主part_code){tempName}  on 主part_code = 料号";

            SqlHelper.SqlHelper.ExecuteNonQuery(sqlmes金额);

            //U8 材料金额
            string sqlU8材料金额 = $@"update Z_半成品金额报表 set 材料金额 =part_amount
                                from Z_半成品金额报表 join (select 主part_code, sum(part_amount)part_amount from {tempName} where 账套 <> 'mes' and part_code not like '%#%' group by 主part_code){tempName}  on 主part_code = 料号";

            SqlHelper.SqlHelper.ExecuteNonQuery(sqlU8材料金额);

            //合计金额
            string sql合计金额 = $@"update Z_半成品金额报表 set 合计金额 =part_amount
                                from Z_半成品金额报表 join (select 主part_code, sum(part_amount)part_amount from {tempName} where  part_code not like '%#%'  group by 主part_code){tempName}  on 主part_code = 料号";

            SqlHelper.SqlHelper.ExecuteNonQuery(sql合计金额);

            string sql单价 = $@"update Z_半成品金额报表  set 加工单价 = 加工金额/加工数量 where 加工数量 > 0;

                            update Z_半成品金额报表  set 材料单价 = 材料金额/材料数量 where 材料数量 > 0;

                            update Z_半成品金额报表  set 合计单价 = ISNULL(合计金额,0)/nullif(加工数量,0)";

            SqlHelper.SqlHelper.ExecuteNonQuery(sql单价);
        }

        private void simpleButton查原始数据_Click(object sender, EventArgs e)
        {
            guid = Guid.NewGuid();
            tempName = "temp_" + guid.ToString("N");
           DataTable tb = 查询原始表到temp表();
            gridView1.Columns.Clear();
            this.gridControl1.DataSource = tb;
            this.gridControl1.Refresh();
            清空中间表();
        }

        private void simpleButton导出_Click(object sender, EventArgs e)
        {
            string path;
            saveFileDialog1.Filter = "Excel文件 (*.XLSX)|*.XLSX";
            if (this.saveFileDialog1.ShowDialog(this) == DialogResult.OK)
            {
                path = this.saveFileDialog1.FileName;
                if (!File.Exists(path))
                {
                    //MiniExcel.SaveAs(path, table, true, "sheet1", ExcelType.XLSX);

                    gridView1.ExportToXlsx(path);
                    MessageBox.Show("导出结束!");

                }
                else
                {
                    MessageBox.Show("文件已经存在,请重试!", "提示", MessageBoxButtons.OK);
                    return;
                }

            }
        }

        private void XtraForm半成品查询_Load(object sender, EventArgs e)
        {
            this.Text = "半成品查询" + Application.ProductVersion;
        }
    }
}